﻿Option Explicit On
Option Strict On
Imports System.Text
Imports System.Data
Imports MySql.Data.MySqlClient
Imports System.Configuration
Public Class frmManagerPerson
    Dim Conn As New MySqlConnection()
    Dim com As New MySqlCommand()
    Dim dr As MySqlDataReader
    Dim dt As DataTable
    Dim sb As New StringBuilder()
    Dim tr As MySqlTransaction
    Dim id As String
    Private Sub frmManagerPerson_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim strConn As String = ConfigurationManager.ConnectionStrings("Equ").ConnectionString
        With Conn
            If .State = ConnectionState.Open Then .Close()
            .ConnectionString = strConn
            .Open()
        End With
        ShowData()
        FormatData()
        ShowStaff()
        FormatStaff()
    End Sub

    Private Sub ShowData()
        sb.Remove(0, sb.Length)
        sb.Append("SELECT * FROM member")
        Dim sql As String = sb.ToString()
        com.CommandType = CommandType.Text
        com.CommandText = sql
        com.Connection = Conn
        dr = com.ExecuteReader()
        If dr.HasRows() Then
            dt = New DataTable()
            dt.Load(dr)
            data.DataSource = dt
        Else
            data.DataSource = Nothing
        End If
        dr.Close()
    End Sub

    Private Sub FormatData()
        If data.RowCount > 0 Then
            With data
                .Columns(0).Visible = False
                .Columns(1).HeaderText = "ชื่อ-สกุล"
                .Columns(2).HeaderText = "สถานะ"
                .Columns(3).Visible = False
                .Columns(1).Width = 150
                .Columns(2).Width = 240
            End With
        End If
    End Sub

    Private Sub ShowStaff()
        sb.Remove(0, sb.Length)
        sb.Append("SELECT * FROM staff")
        Dim sql As String = sb.ToString()
        com.CommandType = CommandType.Text
        com.CommandText = sql
        com.Connection = Conn
        dr = com.ExecuteReader()
        If dr.HasRows() Then
            dt = New DataTable()
            dt.Load(dr)
            data_staff.DataSource = dt
        Else
            data_staff.DataSource = Nothing
        End If
        dr.Close()

    End Sub

    Private Sub FormatStaff()
        If data_staff.RowCount > 0 Then
            With data_staff
                .Columns(0).Visible = False
                .Columns(1).HeaderText = "ชื่อ-สกุล"
                .Columns(2).HeaderText = "Username"
                .Columns(3).Visible = False
                .Columns(1).Width = 150
                .Columns(2).Width = 350
            End With
        End If
    End Sub

    Private Sub txtSearch_KeyDown(sender As Object, e As KeyEventArgs) Handles txtSearch.KeyDown
        If txtSearch.Text.Trim() = "" Then
            Exit Sub
        End If
        If e.KeyCode = Keys.Enter Then
            sb.Remove(0, sb.Length)
            sb.Append("SELECT Name,Position FROM member WHERE Name LIKE '%" & txtSearch.Text & "%'")
            Dim sql As String = sb.ToString()
            With com
                .CommandType = CommandType.Text
                .CommandText = sql
                .Connection = Conn
                dr = .ExecuteReader()
                If dr.HasRows Then
                    dt = New DataTable()
                    dt.Load(dr)
                    data.DataSource = dt
                Else
                    data.DataSource = Nothing
                End If
                dr.Close()
            End With

        End If
    End Sub

    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        sb.Remove(0, sb.Length)
        sb.Append("SELECT Name,Position FROM member WHERE Name LIKE '%" & txtSearch.Text & "%'")
        Dim sql As String = sb.ToString()
        With com
            .CommandType = CommandType.Text
            .CommandText = sql
            .Connection = Conn
            dr = .ExecuteReader()
            If dr.HasRows Then
                dt = New DataTable()
                dt.Load(dr)
                data.DataSource = dt
            Else
                data.DataSource = Nothing
            End If
            dr.Close()
        End With
    End Sub

    Private Sub txtSearch_staff_KeyDown(sender As Object, e As KeyEventArgs) Handles txtSearch_staff.KeyDown
        If txtSearch_staff.Text.Trim() = "" Then
            Exit Sub
        End If

        If e.KeyCode = Keys.Enter Then
            sb.Remove(0, sb.Length)
            sb.Append("SELECT Name,Username FROM staff WHERE Name LIKE '%" & txtSearch_staff.Text & "%'")
            Dim sql As String = sb.ToString()
            With com
                .CommandType = CommandType.Text
                .CommandText = sql
                .Connection = Conn
                dr = .ExecuteReader()
                If dr.HasRows Then
                    dt = New DataTable()
                    dt.Load(dr)
                    data.DataSource = dt
                Else
                    data.DataSource = Nothing
                End If
                dr.Close()
            End With
        End If
    End Sub

    Private Sub btnSearch_staff_Click(sender As Object, e As EventArgs) Handles btnSearch_staff.Click
        sb.Remove(0, sb.Length)
        sb.Append("SELECT Name,Username FROM staff WHERE Name LIKE '%" & txtSearch_staff.Text & "%'")
        Dim sql As String = sb.ToString()
        With com
            .CommandType = CommandType.Text
            .CommandText = sql
            .Connection = Conn
            dr = .ExecuteReader()
            If dr.HasRows Then
                dt = New DataTable()
                dt.Load(dr)
                data.DataSource = dt
            Else
                data.DataSource = Nothing
            End If
            dr.Close()
        End With
    End Sub

    Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
        If txtID.Text.Trim() = "" Or txtName.Text = "" Or txtEmail.Text.Trim() = "" Then
            MessageBox.Show("กรุณาป้อนข้อมูลให้ครบด้วย !!!", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        If MessageBox.Show("คุณต้องการเพิ่มบุคลากรใหม่ ใช่หรือไม่ ?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            tr = Conn.BeginTransaction()
            Try
                sb.Remove(0, sb.Length)
                sb.Append("INSERT INTO member(Member_ID,Name,position,email) ")
                sb.Append("VALUES (@Member_ID,@Name,@position,@email)")
                Dim sql As String = sb.ToString()
                With com
                    .CommandText = sql
                    .CommandType = CommandType.Text
                    .Connection = Conn
                    .Transaction = tr
                    .Parameters.Clear()
                    .Parameters.Add("@Member_ID", MySqlDbType.VarChar).Value = txtID.Text.Trim()
                    .Parameters.Add("@Name", MySqlDbType.VarChar).Value = txtName.Text
                    .Parameters.Add("@position", MySqlDbType.VarChar).Value = cbbStatus.Text
                    .Parameters.Add("@email", MySqlDbType.VarChar).Value = txtEmail.Text.Trim()
                    .ExecuteNonQuery()
                End With
                tr.Commit()
                MessageBox.Show("เพิ่มข้อมูลบุคลากรเรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
                ClearAllData()
                ShowData()
                FormatData()
            Catch ex As Exception
                MessageBox.Show("การเพิ่มข้อมูลผิดพลาด !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                tr.Rollback()
            End Try
            txtID.Focus()
        End If
    End Sub

    Private Sub ClearAllData()
        txtID.Clear()
        txtName.Clear()
        txtName_staff.Clear()
        txtPassword_staff.Clear()
        txtSearch.Clear()
        txtSearch_staff.Clear()
        txtUsername.Clear()
        txtConfirm.Clear()
    End Sub

    Private Sub data_CellMouseUp(sender As Object, e As DataGridViewCellMouseEventArgs) Handles data.CellMouseUp
        If e.RowIndex = -1 Then
            Exit Sub
        End If
        txtID.Text = data.Rows(e.RowIndex).Cells("Member_ID").Value.ToString()
        txtID.Enabled = False
        txtName.Text = data.Rows(e.RowIndex).Cells("Name").Value.ToString()
        cbbStatus.Text = data.Rows(e.RowIndex).Cells("position").Value.ToString()
        txtEmail.Text = data.Rows(e.RowIndex).Cells("email").Value.ToString()
        txtName.Focus()
        txtName.SelectAll()
        btnEdit.Enabled = True
    End Sub

    Private Sub btnEdit_Click(sender As Object, e As EventArgs) Handles btnEdit.Click
        If txtID.Text.Trim() = "" Then
            MessageBox.Show("กรุณาป้อนรหัสบุคลากรก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtID.Focus()
            Exit Sub
        End If
        If txtName.Text.Trim() = "" Then
            MessageBox.Show("กรุณาป้อนชื่อบุคลากรก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtName.Focus()
            Exit Sub
        End If
        If txtEmail.Text.Trim() = "" Then
            MessageBox.Show("กรุณาป้อนอีเมล์ก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtEmail.Focus()
            Exit Sub
        End If
        If MessageBox.Show("คุณต้องการแก้ไขข้อมูลบุคลากร ใช่หรือไม่ ?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            tr = Conn.BeginTransaction()
            Try
                sb.Remove(0, sb.Length)
                sb.Append("UPDATE member ")
                sb.Append("SET Name=@Name,position=@position,email=@email ")
                sb.Append("WHERE Member_ID=@member_id")
                Dim sql As String = sb.ToString()
                With com
                    .CommandType = CommandType.Text
                    .CommandText = sql
                    .Connection = Conn
                    .Transaction = tr
                    .Parameters.Clear()
                    .Parameters.Add("@member_id", MySqlDbType.VarChar).Value = txtID.Text.Trim()
                    .Parameters.Add("@Name", MySqlDbType.VarChar).Value = txtName.Text
                    .Parameters.Add("@position", MySqlDbType.VarChar).Value = cbbStatus.Text
                    .Parameters.Add("@email", MySqlDbType.VarChar).Value = txtEmail.Text.Trim()
                    Dim result As Integer = .ExecuteNonQuery()
                    If result = 0 Then
                        tr.Rollback()
                        MessageBox.Show("รหัสไม่ถูกต้อง", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Exit Sub
                    Else
                        tr.Commit()
                        MessageBox.Show("แก้ไขข้อมูลเรียบร้อย !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        ClearAllData()
                        ShowData()
                        FormatData()
                    End If
                End With
            Catch ex As Exception
                tr.Rollback()
                MessageBox.Show("เกิดข้อผิดพลาด เนื่องจาก " & ex.Message, "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End If
        txtID.Focus()
        btnEdit.Enabled = False
    End Sub

    Private Sub btnAdd_staff_Click(sender As Object, e As EventArgs) Handles btnAdd_staff.Click
        If txtName_staff.Text.Trim() = "" Or txtUsername.Text = "" Or txtPassword_staff.Text.Trim() = "" Or txtConfirm.Text.Trim() = "" Then
            MessageBox.Show("กรุณาป้อนข้อมูลให้ครบด้วย !!!", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        If txtPassword_staff.Text.Trim() <> txtConfirm.Text.Trim() Then
            MessageBox.Show("คุณกรอกข้อมูลรหัสผ่านไม่ตรงกัน กรุณากรอกใหม่ !!!", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtConfirm.Clear()
            txtConfirm.Focus()
        End If
        If MessageBox.Show("คุณต้องการเพิ่มเจ้าหน้าที่ใหม่ ใช่หรือไม่ ?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            tr = Conn.BeginTransaction()
            Try
                sb.Remove(0, sb.Length)
                sb.Append("INSERT INTO staff(Name,Username,Password) ")
                sb.Append("VALUES (@Name,@Username,@Password)")
                Dim sql As String = sb.ToString()
                With com
                    .CommandText = sql
                    .CommandType = CommandType.Text
                    .Connection = Conn
                    .Transaction = tr
                    .Parameters.Clear()
                    .Parameters.Add("@Name", MySqlDbType.VarChar).Value = txtName_staff.Text
                    .Parameters.Add("@Username", MySqlDbType.VarChar).Value = txtUsername.Text.Trim()
                    .Parameters.Add("@Password", MySqlDbType.VarChar).Value = txtPassword_staff.Text.Trim()
                    .ExecuteNonQuery()
                End With
                tr.Commit()
                MessageBox.Show("เพิ่มข้อมูลเจ้าหน้าที่เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
                ClearAllData()
                ShowStaff()
                FormatStaff()
            Catch ex As Exception
                MessageBox.Show("การเพิ่มข้อมูลผิดพลาด !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                tr.Rollback()
            End Try
            txtName_staff.Focus()
        End If
    End Sub

    Private Sub data_staff_CellMouseUp(sender As Object, e As DataGridViewCellMouseEventArgs) Handles data_staff.CellMouseUp
        If e.RowIndex = -1 Then
            Exit Sub
        End If
        txtName_staff.Text = data_staff.Rows(e.RowIndex).Cells("Name").Value.ToString()
        txtUsername.Text = data_staff.Rows(e.RowIndex).Cells("Username").Value.ToString()
        txtPassword_staff.Text = data_staff.Rows(e.RowIndex).Cells("Password").Value.ToString()
        txtConfirm.Text = data_staff.Rows(e.RowIndex).Cells("Password").Value.ToString()
        id = data_staff.Rows(e.RowIndex).Cells("idStaff").Value.ToString()
        btnEdit_staff.Enabled = True
    End Sub

    Private Sub btnEdit_staff_Click(sender As Object, e As EventArgs) Handles btnEdit_staff.Click
       
        If txtName_staff.Text.Trim() = "" Then
            MessageBox.Show("กรุณาป้อนชื่อเจ้าหน้าที่ก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtName.Focus()
            Exit Sub
        End If
        If txtUsername.Text.Trim() = "" Then
            MessageBox.Show("กรุณาป้อน Username ก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtEmail.Focus()
            Exit Sub
        End If
        If txtPassword_staff.Text.Trim() = "" Or txtConfirm.Text.Trim() = "" Then
            MessageBox.Show("กรุณาป้อน Password ก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtPassword_staff.Focus()
            Exit Sub
        End If
        If txtPassword_staff.Text.Trim() <> txtConfirm.Text.Trim() Then
            MessageBox.Show("รหัสผ่านไม่ตรงกัน กรุณาแก้ไขด้วย !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtConfirm.SelectAll()
            txtConfirm.Focus()
            Exit Sub
        End If
        If MessageBox.Show("คุณต้องการแก้ไขข้อมูลเจ้าหน้าที่ ใช่หรือไม่ ?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            tr = Conn.BeginTransaction()
            Try
                sb.Remove(0, sb.Length)
                sb.Append("UPDATE staff ")
                sb.Append("SET Name=@Name,Username=@username,Password=@password ")
                sb.Append("WHERE idStaff=@staff_id")
                Dim sql As String = sb.ToString()
                With com
                    .CommandType = CommandType.Text
                    .CommandText = sql
                    .Connection = Conn
                    .Transaction = tr
                    .Parameters.Clear()
                    .Parameters.Add("@staff_id", MySqlDbType.Int32).Value = CInt(id)
                    .Parameters.Add("@Name", MySqlDbType.VarChar).Value = txtName_staff.Text
                    .Parameters.Add("@username", MySqlDbType.VarChar).Value = txtUsername.Text
                    .Parameters.Add("@password", MySqlDbType.VarChar).Value = txtPassword_staff.Text.Trim()
                    Dim result As Integer = .ExecuteNonQuery()
                    If result = 0 Then
                        tr.Rollback()
                        MessageBox.Show("รหัสไม่ถูกต้อง", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Exit Sub
                    Else
                        tr.Commit()
                        MessageBox.Show("แก้ไขข้อมูลเรียบร้อย !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        ClearAllData()
                        ShowStaff()
                        FormatStaff()
                    End If
                End With
            Catch ex As Exception
                tr.Rollback()
                MessageBox.Show("เกิดข้อผิดพลาด เนื่องจาก " & ex.Message, "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End If
        txtName_staff.Focus()
        btnEdit.Enabled = False
    End Sub
End Class